/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package t5.model;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Gabriel Lunardi
 */
public class AlunoDAO {

    public Connection abrir() {
        Connection c = null;
        try {
            Class.forName(BD.JDBC_DRIVER);
            c = DriverManager.getConnection(BD.URL_CONEXAO, BD.USUARIO, BD.SENHA);
        } catch (ClassNotFoundException ecnf) {
            System.out.println("erro ao carregar driver JDBC " + BD.JDBC_DRIVER + " na memória!");
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return c;
    }

    public List<Aluno> buscarTodos() {
        Connection conexao = abrir();
        List<Aluno> alunos = new ArrayList<Aluno>();
        try {
            Statement s = conexao.createStatement();
            ResultSet rs = s.executeQuery("SELECT * FROM aluno");
            while (rs.next()) {
                Aluno temp = new Aluno(rs.getInt("mat"), rs.getString("nome"), rs.getString("anoIngresso"),listDisciplinasByAluno(rs.getInt("mat")));
                alunos.add(temp);
            }
            conexao.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return alunos;
    }

    public List<Disciplina> listDisciplinasByAluno(int matricAluno) {
        Connection conexao = abrir();
        List<Disciplina> discips = new ArrayList<Disciplina>();
        String sql =
                "SELECT d.* "
                + "FROM disciplina d, aluno_disc ad "
                + "WHERE ad.matricula = ? "
                + "AND d.coddiscip = ad.coddisc";
        PreparedStatement pstm = null;
        ResultSet rs = null;
        try {
            pstm = conexao.prepareStatement(sql);
            pstm.setInt(1, matricAluno);
            rs = pstm.executeQuery();

            while (rs.next()) {
                Disciplina disc = new Disciplina(rs.getString("codDiscip"), rs.getString("nome"), rs.getInt("cargaHoraria"), rs.getInt("sem_acon"));
                discips.add(disc);
            }
            conexao.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return discips;
    }
}
